In a relational database management system, data is stored in a multiplicity of tables having a multiplicity of rows (records), the rows having a multiplicity of columns (fields). A subset of the columns is designated as key columns and the combination of values of the key columns of the rows of a single table must be distinct. It is frequently desirable to maintain copies of a table residing in a database of the relational database system in other databases in the system. Furthermore, it is desirable that changes (inserts, deletes, and updates) to rows in one table copy in the database be copied or replicated to the other table copies residing in the other databases.
The propagation of changes made to one table copy may be synchronous or asynchronous to the original change. Synchronous propagation makes changes at all table copies as part of the same transaction that initiated the original changes. Synchronous change propagation requires that the database management systems maintaining all or most table copies be active and available at the time of the change. Also, synchronous change propagation introduces substantial messaging and synchronization costs at the time of the original changes. Asynchronous propagation copies the original changes to the other table copies in separate transactions, subsequent to the completion of the transaction initiating the original changes. Thus, asynchronous change propagation is sometimes more desirable due to its savings in overhead costs.
The detecting of changes to be propagated asynchronously can be active or passive. Active change detection isolates the changes, at the time of the change, for later processing using database triggers or a similar mechanism. Passive change detection exploits information from the database recovery log, where changes are recorded for other purposes, to deduce what rows of which tables were changed as well as both the old and new values of changed columns. When changes are allowed to be initiated at any table copy, the system has been variously called “multi-master”, “peer-to-peer”, and “update anywhere” data replication.
However, asynchronous propagation of changes to any of the table copies introduces the possibility of conflicting changes, and the need for their resolution if the multiple table copies are to attain the same state. Some conventional solutions to the conflicting changes depend on a designated “master” copy at which conflicts are resolved. Other conventional solutions depend upon maintaining a history, or version vector, of change times for each copy of each row. Other conventional solutions do not insure that all copies of the table will converge to the same state.
Accordingly, there exists a need for a method and system for providing convergence of data copies in asynchronous data replication in a database system. The method and system should decrease the processing and storage costs, not require a designated “master” copy, not require that all table copies be simultaneously available at any time, ensure that all table copies will converge to a same state representing the most recent changes from any table copy, and/or report conflicting changes and how they are resolved. The present invention addresses such a need.